package com.lps.util.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.*;

@Slf4j
public class ExcelUtil {


        public static void downloadReport(SXSSFWorkbook workbook) {
            //log.info("ReadReportByPage() end count:{}", reportList.size());
            //----------------------------------------------------------

            CellStyle cellFirstTitle = workbook.createCellStyle();
            Font font1 = workbook.createFont();
            font1.setFontName("宋体");
            font1.setFontHeightInPoints((short) 16);
            font1.setBold(true);
            cellFirstTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
            cellFirstTitle.setAlignment(HorizontalAlignment.CENTER);//水平
            cellFirstTitle.setBorderBottom(BorderStyle.THIN); //下边框
            cellFirstTitle.setBorderLeft(BorderStyle.THIN);//左边框
            cellFirstTitle.setBorderTop(BorderStyle.THIN);//上边框
            cellFirstTitle.setBorderRight(BorderStyle.THIN);//右边框
            cellFirstTitle.setFont(font1);

            //标题单元格样式
            CellStyle cellStyleTitle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            font.setBold(true);
            cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
            cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);//水平
            cellStyleTitle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyleTitle.setFont(font);

            //样式
            CellStyle cellStyle = workbook.createCellStyle();
            font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setFont(font);

            //创建sheet页
            SXSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(0, "统计信息");

            //设置列宽
            sheet.setColumnWidth(0, 10000);
            sheet.setColumnWidth(1, 5000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 5000);
            sheet.setColumnWidth(4, 3000);
            sheet.setColumnWidth(5, 5000);
            sheet.setColumnWidth(6, 4000);
            sheet.setColumnWidth(7, 5000);
            sheet.setColumnWidth(8, 4000);

            //标题，第0行
            Row row = sheet.createRow(0);
            row.setHeight((short) 800);
            Cell cell = null;
            for(int i = 0 ; i <= 8; ++i) {
                cell = row.createCell(i);
                cell.setCellValue("企业消息交换服务周报表");
                cell.setCellStyle(cellFirstTitle);
            }
            sheet.addMergedRegionUnsafe(new CellRangeAddress(0,0,0, 8));

            //列名，第1行
            row = sheet.createRow(1);
            cell = row.createCell(0);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("主题");

            cell = row.createCell(1);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("主题说明");

            cell = row.createCell(2);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("发送方");

            cell = row.createCell(3);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("接收方");

            cell = row.createCell(4);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("日期");

            cell = row.createCell(5);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("消息数量(条)");

            cell = row.createCell(6);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("消息量");

            cell = row.createCell(7);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("总量/日均TPS");

            cell = row.createCell(8);
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue("日均流量");


            Map<String,List<ReportDomain>> reportMap = new HashMap<>();
            List<ReportDomain> reportList = new ArrayList<>(50);
            //填充数据
            int rowCount = 1;
            Iterator<String> ite = reportMap.keySet().iterator();
            while (ite.hasNext()) {
                String topic = ite.next();

                List<ReportDomain> days = reportMap.get(topic);
                if(CollectionUtils.isEmpty(days)) {
                    continue;
                }

                //计算日均TPS与计算日均流量
                long sumTmp = 0;
                long sizeSumTmp = 0;

                for(int t = 0;  t < days.size(); ++t) {
                    ReportDomain rep = days.get(t);
                    sumTmp = sumTmp + rep.getDaySum();
                    sizeSumTmp = sizeSumTmp + rep.getDaySumSize();
                }
                double dayTps = sumTmp/(days.size() + 0.0);
                double daySize = sizeSumTmp/(days.size() + 0.0);
                int startIndex = rowCount + 1;
                for(int i = 0; i < days.size(); ++i) {
                    ReportDomain item = days.get(i);
                    row = sheet.createRow(++rowCount);
                    //列0，主题
                    cell = row.createCell(0);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(topic);
                    //列1，主题说明
                    cell = row.createCell(1);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getTopicMem());
                    //列2，发送方
                    cell = row.createCell(2);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getSend() + "");
                    //列3，接收方
                    cell = row.createCell(3);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getRecv() + "");
                    //列4，日期
                    cell = row.createCell(4);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getReportDate());
                    //列5，消息数量(条)
                    cell = row.createCell(5);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(item.getDaySum());
                    //列6，消息量
                    cell = row.createCell(6);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(humanReadableByteCount(item.getDaySumSize(), false));
                    //列7，消息总量/日均TPS
                    cell = row.createCell(7);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(sumTmp + "/" + String.format("%.2f",dayTps));
                    //列7，日均流量
                    cell = row.createCell(8);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(humanReadableByteCount((long) daySize, false));
                } // data for ends.

                // 只在多行的情况下，才可以合并单元格
                if(days.size() > 1) {
                    // 合并区域名称单元格
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 0, 0));
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 1, 1));
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 2, 2));
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 3, 3));
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 7, 7));
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startIndex, rowCount, 8, 8));
                }
            } // while ends.

        }


        private static String humanReadableByteCount(long bytes, boolean si) {
            int unit = si ? 1000 : 1024;
            if (bytes < unit)
                return bytes + " B";
            int exp = (int) (Math.log(bytes) / Math.log(unit));
            String pre = (si ? "kMGTPE" : "KMGTPE").charAt(exp - 1) + (si ? "" : "i");
            return String.format("%.1f %sB", bytes / Math.pow(unit, exp), pre);
        }


         public static void main(String[] args) {
             SXSSFWorkbook workbook = new SXSSFWorkbook(100);
             downloadReport(workbook);
        }
    }

